package com.ejuornal.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.ejuornal.bean.Category;
import com.ejuornal.bean.Page;
import com.ejuornal.bean.Periodical;
import com.ejuornal.bean.vo.VO_SubScribe;
import com.ejuornal.dao.PeriodicalDao;

@Component("periodicalDao")
public class PeriodicalDaoImpl extends DaoSupport<Periodical> implements PeriodicalDao {
	public void add(String sql, Object... objs) {
		super.add(sql, objs);
	}

	public void batchDelete(String sql, int... ids) {
		super.batchDelete(sql, ids);
	}

	public void update(String sql, Object... objs) {
		super.update(sql, objs);
	}
	//得到期刊的详细信息，及它属于哪个分类
	public Periodical findById(int periodicalId) {
		String sql = "SELECT p.*,c.name as cname FROM t_periodical p INNER JOIN t_category c ON p.categoryId=c.categoryId WHERE periodicalId=?";
		Periodical periodical = super.getJdbcTemplate().queryForObject(sql,
				new Object[] { periodicalId }, new RowMapper<Periodical>() {

					public Periodical mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Periodical periodical= new Periodical();
						periodical.setPeriodicalId(rs.getInt("periodicalId"));
						periodical.setUserId(rs.getInt("userId"));
						periodical.setName(rs.getString("name"));
						periodical.setCycle(rs.getString("cycle"));
						periodical.setCreateDate(rs.getDate("createDate"));
						periodical.setPubDate(rs.getString("pubDate"));
						periodical.setIntroduce(rs.getString("introduce"));
						periodical.setHostUnit(rs.getString("hostUnit"));
						periodical.setCover(rs.getString("cover"));
						periodical.setTag(rs.getString("tag"));
						periodical.setStatus(rs.getString("status"));
						periodical.setCategoryId(rs.getInt("categoryId"));
						
						Category category = new Category();
						category.setName(rs.getString("cname"));
						periodical.setCategory(category);
						return periodical;
					}
				});

		return periodical;
	}
	
	 public List<Periodical> findTop(int count) {
	        String sql = "SELECT * FROM t_periodical AS p INNER JOIN t_per_periodical AS pp ON p.periodicalId=pp.periodicalId where p.status=1 AND pp.status=1 ORDER BY createDate DESC LIMIT 0,?";
	        List<Periodical> periodicals = super.getJdbcTemplate().query(sql, 
	                 new Object[]{count},
	                 new RowMapper<Periodical>() {
	                    public Periodical mapRow(ResultSet rs, int arg1) throws SQLException {
	                        Periodical p = new Periodical();
	                        p.setPeriodicalId(rs.getInt("periodicalId"));
	                        p.setName(rs.getString("name"));
	                        p.setCover(rs.getString("cover"));
	                        return p;
	                      }
	                    } 
	                );
	        return periodicals;
	    }
    @Override
    /**
     * 这个地方还没有添加审核的环节
     */
    public List<Periodical> findByCategoryId(int id, String status) {
        String sql = "SELECT * FROM t_periodical WHERE categoryId=? AND status=?";
        List<Periodical> periodicals = super.getJdbcTemplate().query(sql, 
                    new Object[]{ id, status },
                    new RowMapper<Periodical>() {
                        public Periodical mapRow(ResultSet rs, int arg1) throws SQLException {
                            Periodical p = new Periodical();
                            p.setPeriodicalId(rs.getInt("periodicalId"));
                            p.setName(rs.getString("name"));
                            p.setCover(rs.getString("cover"));
                            return p;
                        }
            
                    });
        return periodicals;
    }

    @Override
    public List<Periodical> findByName(String title) {
        String sql = "SELECT * FROM t_periodical WHERE status=1 AND name like ? ORDER BY pubDate DESC LIMIT 0, 10";
        List<Periodical> periodicals = super.getJdbcTemplate().query(sql, new Object[]{"%"+title+"%"},
                new RowMapper<Periodical>() {
                    public Periodical mapRow(ResultSet rs, int arg1) throws SQLException {
                        Periodical p = new Periodical();
                        p.setPeriodicalId(rs.getInt("periodicalId"));
                        p.setName(rs.getString("name"));
                        p.setCover(rs.getString("cover"));
                        
                        return p;
                    }
                });
        return periodicals;
    }

	@Override
	public List<Periodical> findByEditorChiefId(int id) {
		String sql ="select * from t_periodical where userId = ? and status = 1";
		 List<Periodical> list = super.getJdbcTemplate().query(sql, 
                 new Object[]{id},
                 new RowMapper<Periodical>() {
                     public Periodical mapRow(ResultSet rs, int arg1) throws SQLException {
                         Periodical p = new Periodical();
                         p.setPeriodicalId(rs.getInt("periodicalId"));
                         p.setName(rs.getString("name"));
                         p.setUserId(rs.getInt("userId"));
                         return p;
                     }
         
                 });
        return list;
	}
	
	public Page<Periodical> find(int current_record, String url, String sql_1, String sql_2, Object[] objs) {
		return find(current_record, url, 10, sql_1, sql_2,objs);
	}
	
	public Page<Periodical> find(int current_record, String url, int pageSize, String sql_1, String sql_2, Object[] objs) {
		Page<Periodical> page = new Page<Periodical>();
        @SuppressWarnings("deprecation")
		int totalRecords = this.getJdbcTemplate().queryForInt(sql_1, objs);
		List<Periodical> list = (List<Periodical>) this.getJdbcTemplate().query(sql_2, objs, new RowMapper<Periodical>(){

			@Override
			public Periodical mapRow(ResultSet rs, int rowNum) throws SQLException {
				Periodical periodical = new Periodical();
				periodical.setPeriodicalId(rs.getInt("periodicalId"));
				periodical.setName(rs.getString("name"));
				periodical.setCycle(rs.getString("cycle"));
				periodical.setCreateDate(rs.getDate("createDate"));
				periodical.setStatus(rs.getString("status"));
				
				Category category = new Category();
				category.setName(rs.getString("cname"));
				
				periodical.setCategory(category);
				
				return periodical;
			}});
        page.setPageSize(pageSize);
        page.setUrl(url);
        page.setTotalRecords(totalRecords);
        page.setTotalPage(totalRecords);
        page.setRecords(list);
		
		return page;
	}

	@Override
	public Periodical findPeriodicalByPerId(int perPeriodicalId) {
		String sql = "select p.* from t_periodical p where p.periodicalId in ("+
						"SELECT pp.periodicalId from t_per_periodical pp where pp.perPeriodicalId =?)";
		Periodical periodical = super.getJdbcTemplate().queryForObject(sql, new Object[] { perPeriodicalId }, new PeriodicalMapper());
					
		return periodical;
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public List<VO_SubScribe> find() {
		String sql = "select t1.perPeriodicalid, t2.number, t3.name as perPeriodicalName, t3.periodicalId " +
				     "from t_per_periodical_sub t1, t_per_periodical t2, t_periodical t3 " +
				     "where t1.perPeriodicalid=t2.perPeriodicalId and t1.periodicalId = t3.periodicalId " +
				     "and t1.status=1";
		
		List<VO_SubScribe> vvList = getJdbcTemplate().query(sql, new RowMapper(){
					public Object mapRow(ResultSet rs, int arg1) throws SQLException {
						VO_SubScribe v = new VO_SubScribe();
						
						v.setNumber(rs.getInt("number"));
						v.setPeriodicalId(rs.getInt("periodicalId"));
						v.setPeriodicalName(rs.getString("perPeriodicalName"));
						v.setPerPeriodicalId(rs.getInt("perPeriodicalid"));
						return v;
					}
		});
		return vvList;
	}
	
	@Override
	public List<Periodical> find(String sql, Object[] objs) {
		List<Periodical> periodicals = super.getJdbcTemplate().query(sql, objs, new PeriodicalMapper());
		
		return periodicals;
	}

	@Override
	public Periodical findPeriodical(int articalId,String sql) {
		Periodical periodical = super.getJdbcTemplate().queryForObject(sql,
				new Object[] { articalId }, new RowMapper<Periodical>() {

					public Periodical mapRow(ResultSet rs, int rowNum) throws SQLException {
						Periodical periodical= new Periodical();
						periodical.setPeriodicalId(rs.getInt("periodicalId"));
						periodical.setUserId(rs.getInt("userId"));
						periodical.setName(rs.getString("name"));
						periodical.setCycle(rs.getString("cycle"));
						periodical.setCreateDate(rs.getDate("createDate"));
						periodical.setPubDate(rs.getString("pubDate"));
						periodical.setIntroduce(rs.getString("introduce"));
						periodical.setHostUnit(rs.getString("hostUnit"));
						periodical.setCover(rs.getString("cover"));
						periodical.setTag(rs.getString("tag"));
						periodical.setStatus(rs.getString("status"));
						return periodical;
					}
				});

					
		return periodical;
	}
}

final class PeriodicalMapper implements RowMapper<Periodical> {

	@Override
	public Periodical mapRow(ResultSet rs, int rowNum) throws SQLException {
		Periodical periodical= new Periodical();
		periodical.setPeriodicalId(rs.getInt("periodicalId"));
		periodical.setUserId(rs.getInt("userId"));
		periodical.setName(rs.getString("name"));
		periodical.setCategoryId(rs.getInt("categoryId"));
		periodical.setCycle(rs.getString("cycle"));
		periodical.setCreateDate(rs.getDate("createDate"));
		periodical.setPubDate(rs.getString("pubDate"));
		periodical.setIntroduce(rs.getString("introduce"));
		periodical.setHostUnit(rs.getString("hostUnit"));
		periodical.setCover(rs.getString("cover"));
		periodical.setTag(rs.getString("tag"));
		periodical.setStatus(rs.getString("status"));
		
		return periodical;
	}
}
